In [1]:
import pandas as pd
import numpy as np
In [2]:
import pandas as pd

# Read the Excel file
df = pd.read_excel('Datasets/ElectricityByCounty.xlsx')

# Reshape the DataFrame
df = df.melt(id_vars=['County', 'Sector'],
             var_name='Timestamp', value_name='Yearly Data')

# Convert the "Timestamp" column to datetime format representing the year
df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='%Y')

# Sort the DataFrame by County and Timestamp
df = df.sort_values(['County', 'Timestamp'])

# Print the resulting DataFrame
df_indexd = df.set_index('Timestamp')
df_indexd
Out[2]:
County Sector Yearly Data
Timestamp
1990-01-01 ALAMEDA Non-Residential 7109.299895
1990-01-01 ALAMEDA Residential 2498.265626
1990-01-01 ALAMEDA Total 9607.565521
1991-01-01 ALAMEDA Non-Residential 6809.573768
1991-01-01 ALAMEDA Residential 2515.209970
... ... ... ...
2020-01-01 YUBA Residential 251.861806
2020-01-01 YUBA Total 567.007959
2021-01-01 YUBA Non-Residential 315.822249
2021-01-01 YUBA Residential 260.500675
2021-01-01 YUBA Total 576.322924

5568 rows × 3 columns

In [3]:
import pandas as pd
import plotly.graph_objects as go
from statsmodels.tsa.arima.model import ARIMA
from dateutil.relativedelta import relativedelta
import numpy as np
from pmdarima import auto_arima

# Read the Excel file
df = pd.read_excel('Datasets/ElectricityByCounty.xlsx')

# Reshape the DataFrame
df = df.melt(id_vars=['County', 'Sector'],
             var_name='Timestamp', value_name='Yearly Data')

# Convert the "Timestamp" column to datetime format representing the year
df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='%Y')

# Sort the DataFrame by County and Timestamp
df = df.sort_values(['County', 'Timestamp'])

# Iterate over each country and sector
for country in df['County'].unique():
    for sector in df['Sector'].unique():
        fig = go.Figure()

        # Get the energy consumption data for the current country and sector
        df_filter = df[(df['County'] == country) & (
            df['Sector'] == sector)][['Timestamp', 'Yearly Data']]
        df_filter_index = df_filter.set_index('Timestamp')

        # Prepare the data for modeling
        years = df_filter_index.index
        energy_consumption = df_filter_index.values.flatten()

        # Split the data into training and testing
        # Use all data except the last 5 years for training
        Horizan = -5
        train_data = energy_consumption[:Horizan]
        test_data = energy_consumption[Horizan:]  # Use the last 5 years for testing

        # Fit the auto ARIMA model
        model = auto_arima(train_data, seasonal=False)
        model.fit(train_data)

        # Generate predictions
        predictions = model.predict(n_periods=len(test_data))
        predictions_ahead_in_future = model.predict(n_periods=len(test_data)+16)

        # Plot the training data
        fig.add_trace(go.Scatter(
            x=years[:Horizan], y=train_data, mode='lines+markers', name='Training Data'))

        # Plot the predictions
        fig.add_trace(go.Scatter(
            x=years[Horizan:], y=test_data, mode='lines+markers', name='Testing Data'))
        fig.add_trace(go.Scatter(
            x=years[Horizan:], y=predictions, mode='lines+markers', name='Predictions'))

        fig.add_trace(go.Scatter(
            x=pd.date_range(start = years[Horizan],periods=16,freq='Y'), y=predictions_ahead_in_future, mode='lines+markers', name='Ahead in Future Predictions'))

        # Update the layout
        fig.update_layout(title=f'Energy Consumption Forecast Country : {country} : Sector {sector} ',
                          xaxis_title='Year', yaxis_title='Energy Consumption')

        # Show the plot
        fig.show()
        break
In [4]:
pd.date_range(start = years[Horizan],periods=30,freq='Y')
Out[4]:
DatetimeIndex(['2017-12-31', '2018-12-31', '2019-12-31', '2020-12-31',
               '2021-12-31', '2022-12-31', '2023-12-31', '2024-12-31',
               '2025-12-31', '2026-12-31', '2027-12-31', '2028-12-31',
               '2029-12-31', '2030-12-31', '2031-12-31', '2032-12-31',
               '2033-12-31', '2034-12-31', '2035-12-31', '2036-12-31',
               '2037-12-31', '2038-12-31', '2039-12-31', '2040-12-31',
               '2041-12-31', '2042-12-31', '2043-12-31', '2044-12-31',
               '2045-12-31', '2046-12-31'],
              dtype='datetime64[ns]', freq='A-DEC')